

import com.mysql.jdbc.PreparedStatement;
import db.DbTools;
import model.Emp;

import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamException;
import javax.xml.stream.XMLStreamReader;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;


/**
 * Desc:
 * Create by Myth on 10/9/2018
 */
public class Main {

    public static void main(String[] args) throws FileNotFoundException, XMLStreamException, UnsupportedEncodingException, SQLException {
        // 开始时间 —— 计时器
        Long begin = new Date().getTime();
        // 数据库连接
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/stackoverflow";
        String username = "root";
        String password = "123456";
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }

        // sql语句前缀
        String prefix = "INSERT INTO emp (id, name) VALUES ";

        // sql语句后缀
        StringBuffer suffix = new StringBuffer();
        // 设置事务为非自动提交
        connection.setAutoCommit(false);
        // PreparedStatement 执行 sql语句
        PreparedStatement pst = (PreparedStatement) connection.prepareStatement("");

        // 解析xml获得数据
        XMLInputFactory inputFactory = XMLInputFactory.newInstance();
        String filePath = "Users.xml";
        File file = new File(filePath);

        // 应该使用 FileInputStream, 否则会出现 "Content is not allowed in prolog" error.
        InputStream isS= new FileInputStream(file);
        XMLStreamReader streamReader = inputFactory.createXMLStreamReader(isS);
        String id = null, name = null;
        int count = 0;

        final int COMMIT_COUNT = 30000;

        // 存储数据
        while(streamReader.hasNext()) {
            streamReader.next();
            if(streamReader.getEventType() == XMLStreamReader.START_ELEMENT){
                if (streamReader.getLocalName().equals("row")) {
                    id = streamReader.getAttributeValue(null,"Id");
                    name = streamReader.getAttributeValue(null,"DisplayName");
                    // name.replaceAll("'", "''");
                    suffix.append("(" + id + ", \"" + name + "\"),");
                    count += 1; // 记录行数
                    // System.out.print("Count: " + Integer.toString(count));
                    // System.out.println(prefix + suffix.substring(0, suffix.length() - 1));
                    if (count % COMMIT_COUNT == 0) {
                        // 构建完整sql
                        String sql = prefix + suffix.substring(0, suffix.length() - 1);
                        // 添加执行sql
                        pst.addBatch(sql);
                        // 执行操作
                        pst.executeBatch();
                        // 提交事务
                        connection.commit();
                        // 清空上一次添加的数据
                        suffix = new StringBuffer();
                        System.out.println("Commit: " + count);
                    }
                }
            }
        }

        if (suffix.length() != 0) {
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            pst.addBatch(sql);
            pst.executeBatch();
            connection.commit();
        }

        pst.close();

        // 结束时间
        Long end = new Date().getTime();
        // 耗时
        System.out.println("cast : " + (end - begin) / 1000 + " s");
    }



}
